{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "initial_id",
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    ""
   ]
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# 1、create_sql_query_chain的使用\n",
    "\n",
    "举例1："
   ],
   "id": "b9e33facd848e9dd"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-10-01T12:47:32.062354Z",
     "start_time": "2025-10-01T12:47:31.845522Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# pip install -U langchain langchain-community langchain-openai\n",
    "from langchain_openai import ChatOpenAI\n",
    "from langchain.chains import create_sql_query_chain\n",
    "from langchain_community.utilities import SQLDatabase\n",
    "\n",
    "# 测试连接本地的mysql数据库\n",
    "db_user = \"root\"\n",
    "db_password = \"Zan_123456\"\n",
    "db_host = \"localhost\" #或 127.0.0.1\n",
    "db_port = \"3306\"\n",
    "db_database = \"guiguxiaozhi\"\n",
    "# mysql+pymysql://用户名:密码@ip地址:端口号/数据库名\n",
    "db = SQLDatabase.from_uri(f\"mysql+pymysql://{db_user}:{db_password}@{db_host}:{db_port}/{db_database}\")\n",
    "\n",
    "print(\"操作的是哪种数据库：\",db.dialect)\n",
    "print(\"获取数据库中的表：\",db.get_usable_table_names())\n",
    "\n",
    "#执行查询操作\n",
    "res = db.run(\"SELECT COUNT(*) FROM appointment\")\n",
    "print(res)"
   ],
   "id": "88c56c4c5e700307",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "操作的是哪种数据库： mysql\n",
      "获取数据库中的表： ['appointment']\n",
      "[(2,)]\n"
     ]
    }
   ],
   "execution_count": 2
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "举例2：chain的使用",
   "id": "add3217f88cdc971"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-10-01T12:51:21.913567Z",
     "start_time": "2025-10-01T12:51:20.912669Z"
    }
   },
   "cell_type": "code",
   "source": [
    "\n",
    "# pip install -U langchain langchain-community langchain-openai\n",
    "from langchain_openai import ChatOpenAI\n",
    "from langchain.chains import create_sql_query_chain\n",
    "from langchain_community.utilities import SQLDatabase\n",
    "\n",
    "\n",
    "#1、获取mysql数据库的连接\n",
    "# 测试连接本地的mysql数据库\n",
    "db_user = \"root\"\n",
    "db_password = \"Zan_123456\"\n",
    "db_host = \"localhost\" #或 127.0.0.1\n",
    "db_port = \"3306\"\n",
    "db_database = \"guiguxiaozhi\"\n",
    "# mysql+pymysql://用户名:密码@ip地址:端口号/数据库名\n",
    "db = SQLDatabase.from_uri(f\"mysql+pymysql://{db_user}:{db_password}@{db_host}:{db_port}/{db_database}\")\n",
    "\n",
    "\n",
    "# 2、获取大语言模型\n",
    "import os\n",
    "import dotenv\n",
    "from langchain_openai import ChatOpenAI\n",
    "\n",
    "dotenv.load_dotenv()\n",
    "\n",
    "os.environ['OPENAI_API_KEY'] = os.getenv(\"OPENAI_API_KEY1\")\n",
    "os.environ['OPENAI_BASE_URL'] = os.getenv(\"OPENAI_BASE_URL\")\n",
    "chat_model = ChatOpenAI(model=\"gpt-4o-mini\",verbose= True)\n",
    "\n",
    "# 3、创建create_sql_query_chain的实例\n",
    "chain = create_sql_query_chain(chat_model, db)\n",
    "# response = chain.invoke({\"question\": \"数据表employees中一共有多少个员工？\",\n",
    "#                          \"table_names_to_use\":[\"employees\"]})\n",
    "# print(response)\n",
    "\n",
    "# response = chain.invoke({\"question\": \"数据表employees中薪资最高的员工信息\",\n",
    "#                          \"table_names_to_use\":[\"employees\"]})\n",
    "\n",
    "response = chain.invoke({\"question\": \"数据表appointment中最新预约的是谁\",\n",
    "                         \"table_names_to_use\":[\"appointment\"]})\n",
    "print(response)\n"
   ],
   "id": "f95efaa4cb85faf8",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQLQuery: \n",
      "```sql\n",
      "SELECT `username`, `date`, `time`, `doctor_name` \n",
      "FROM `appointment` \n",
      "ORDER BY `date` DESC, `time` DESC \n",
      "LIMIT 1;\n",
      "```\n"
     ]
    }
   ],
   "execution_count": 4
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# 2、create_stuff_documents_chain的使用",
   "id": "1c11060533780320"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-29T02:01:50.204086Z",
     "start_time": "2025-09-29T02:01:48.661911Z"
    }
   },
   "cell_type": "code",
   "source": [
    "\n",
    "import os\n",
    "import dotenv\n",
    "from langchain.chains.combine_documents import create_stuff_documents_chain\n",
    "from langchain_core.prompts import PromptTemplate\n",
    "from langchain_openai import ChatOpenAI\n",
    "from langchain_core.documents import Document\n",
    "dotenv.load_dotenv()\n",
    "\n",
    "os.environ['OPENAI_API_KEY'] = os.getenv(\"OPENAI_API_KEY1\")\n",
    "os.environ['OPENAI_BASE_URL'] = os.getenv(\"OPENAI_BASE_URL\")\n",
    "# 定义提示词模板\n",
    "prompt = PromptTemplate.from_template(\"\"\"\n",
    "基于文档{docs}中说的情况，香蕉是什么颜色的？\n",
    "\"\"\")\n",
    "\n",
    "# 创建链\n",
    "llm = ChatOpenAI(model=\"gpt-4o-mini\")\n",
    "chain  = create_stuff_documents_chain( llm,prompt,document_variable_name=\"docs\")\n",
    "\n",
    "\n",
    "# 文档输入\n",
    "docs123 = [\n",
    "    Document(\n",
    "        page_content=\"苹果，学名Malus pumila Mill.，别称西洋苹果、柰，属于蔷薇科苹果属的植物。苹果是全球最广泛种植和销售的水果之一，具有悠久的栽培历史和广泛的分布范围。苹果的原始种群主要起源于中亚的天山山脉附近，尤其是现代哈萨克斯坦的阿拉木图地区，提供了所有现代苹果品种的基因库。苹果通过早期的贸易路线，如丝绸之路，从中亚向外扩散到全球各地。\"\n",
    "    ),\n",
    "    Document(\n",
    "        page_content=\"香蕉是白色的水果，主要产自热带地区。\"\n",
    "\n",
    "    ),\n",
    "    Document(\n",
    "        page_content=\"蓝莓是蓝色的浆果，含有抗氧化物质。\"\n",
    "\n",
    "    )\n",
    "]\n",
    "# 执行摘要\n",
    "chain.invoke({\"docs\": docs123})"
   ],
   "id": "843eefb87b92221c",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'根据你提供的信息，香蕉被描述为“白色的水果”。不过，实际上香蕉通常被认为是黄色的，成熟时表皮呈现黄色。可能是描述中的某种误解。通俗来说，香蕉的外观颜色通常是黄色，而内部果肉是白色的。'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 9
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
